package crm.Utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

//这个类能做什么呢？驱动数据库服务器、连接数据库、实现增删改查
public class JDBCUtil {
	private static String name = "root";
	private static String password = "123456";
	private static String url = "jdbc:mysql://127.0.0.1:3306/crm?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone =Asia/Shanghai";

	private static Connection connection=null;
	private PreparedStatement  preparedStatement=null;
	private ResultSet  resultSet=null;
	private static JDBCUtil jdbcUtil=null;
    //懒汉式加载
	public static synchronized JDBCUtil getInitJDBCUtil() {
		if(jdbcUtil==null) {
			jdbcUtil=new JDBCUtil();
		}
		return jdbcUtil;
	}
    //方法：驱动服务器   连接数据库   实现增删改查   关闭数据库 
    public JDBCUtil() {
    	
    }
    //静态方法块：在main方法启动运行后，static静态代码块的语句最先执行
    //加载驱动
    static {
    	try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    
    //连接数据库
    public Connection getConnection() {
    	try {
			connection=DriverManager.getConnection(url,name,password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return connection;
    }
    //实现增删改的方法
    public int executeUpdate(String sql) {
    	int affectedLine=0;
    	try {
			preparedStatement=getConnection().prepareStatement(sql);
			affectedLine=preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll();
		}
    	return affectedLine;
    }
    
    //实现增删改的方法   方法的重载
    public int executeUpdate(String sql,Object[] params) {
    	int affectedLine=0;
    	try {
			preparedStatement=getConnection().prepareStatement(sql);
			for(int i=0;i<params.length;i++) {
				preparedStatement.setObject(i+1, params[i]);
			}
			affectedLine=preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll();
		}
    	return affectedLine;
    }
    private ResultSet executeQueryRS(String sql,Object[] params) {
    	ResultSet rSet=null;
    	try {
			preparedStatement=getConnection().prepareStatement(sql);
			for(int i=0;i<params.length;i++) {
				preparedStatement.setObject(i+1, params[i]);
			}
			rSet=preparedStatement.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rSet;
    }
    
    public ArrayList<HashMap> executeQuery(String sql,Object[] params){
    	//查询结果已经存入结果集rs中了 
    	ResultSet  rs=executeQueryRS(sql, params);
    	//下面的操作只是把结果集rs中的内容存入一个list列表中，
    	//注意列表中的每一个元素都是HashMap数据
    	ResultSetMetaData rsmd=null;
    	int columnCount=0;
    	try {
			rsmd=rs.getMetaData();//取rs中的列
			columnCount=rsmd.getColumnCount();//取列的数目
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	ArrayList<HashMap>  list=new ArrayList<HashMap>();
    	try {
			while(rs.next()) {
				HashMap<String, Object>  map=new HashMap<String,Object>();
				for(int i=1;i<=columnCount;i++) {
					map.put(rsmd.getColumnLabel(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll();
		}
    	return list;
    }
    
    public void closeAll() {
    	if(resultSet!=null) {
    		try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    	
    	if(preparedStatement!=null) {
    		try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    	if(connection!=null) {
    		try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }

}
